﻿Imports System.Net.Mail
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports SAPIntegration.electra_pc
Imports System.Xml

Public Class oImport
    Public Sub Import()
        Try
            Dim connect As New Connection()
            If Connection.bConnect = False Then
                connect.setDB()
                If Not connect.connectDB() Then
                    Functions.WriteLog("SAP Connection Failed")
                Else
                    ItemMasterData()
                    PublicVariable.count = 0
                End If
            Else
                ItemMasterData()
                PublicVariable.count = 0
            End If
        Catch ex As Exception
            PublicVariable.count = 0
            Functions.WriteLog(ex.Message)
        End Try
       
    End Sub
    Public Sub ItemMasterData()
        Try
            Dim myService As New electra_pc.ServiceSoapClient()
            Dim ds As New DataSet()
            Dim myreport As New electra_pc.ServiceSoapClient
            Dim dt As DataTable = myreport.GetItemInfo(PublicVariable.CountryCode, PublicVariable.oCompany.CompanyDB)
            'Dim st As String = myService.GetDBInfo("SG")
            'Dim xn As XmlNode = myService.GetDBInfo("SG")
            'Dim xn As XmlNode = myService.GetItemInfo("SG")
            'ds.ReadXml(New XmlNodeReader(xn))
            Dim oITM As SAPbobsCOM.Items
            Dim oITB As SAPbobsCOM.ItemGroups
            oITM = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oItems)
            oITB = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oItemGroups)
            Dim oRecordSetBP As SAPbobsCOM.Recordset
            oRecordSetBP = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Dim i As Integer = 0
            Dim return_value As Integer = 0
            Dim SerrorMsg As String = ""
            For i = 0 To dt.Rows.Count - 1
                Try
                    oRecordSetBP.DoQuery("SELECT T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] ='" & dt.Rows(i)(1) & "'")
                    If oRecordSetBP.RecordCount = 0 Then
                        oITM.ItemCode = dt.Rows(i)(1) 'ds.Tables("row").Rows(i).Item(1)
                        oITM.ItemName = dt.Rows(i)(2) 'ds.Tables("row").Rows(i).Item(2)
                        oITM.ForeignName = dt.Rows(i)(1)

                        If dt.Rows(i)(20) = "Y" Then
                            oITM.ManageBatchNumbers = SAPbobsCOM.BoYesNoEnum.tYES
                        End If

                        oITM.ItemsGroupCode = ItemGroupCode(dt.Rows(i)(6)) 'ItemGroupCode(ds.Tables("row").Rows(i).Item(6))
                        If dt.Rows(i)(3) = "Y" Then
                            oITM.PurchaseItem = SAPbobsCOM.BoYesNoEnum.tYES
                        Else
                            oITM.PurchaseItem = SAPbobsCOM.BoYesNoEnum.tNO
                        End If
                        If dt.Rows(i)(4) = "Y" Then
                            oITM.SalesItem = SAPbobsCOM.BoYesNoEnum.tYES
                        Else
                            oITM.SalesItem = SAPbobsCOM.BoYesNoEnum.tNO
                        End If
                        If dt.Rows(i)(5) = "Y" Then
                            oITM.InventoryItem = SAPbobsCOM.BoYesNoEnum.tYES
                        Else
                            oITM.InventoryItem = SAPbobsCOM.BoYesNoEnum.tNO
                        End If

                        oITM.UserFields.Fields.Item("U_AB_CreatedDate").Value = dt.Rows(i)(15)
                        oITM.UserFields.Fields.Item("U_AB_UpdateDate").Value = dt.Rows(i)(16)
                        oITM.UserFields.Fields.Item("U_AB_CreatedBy").Value = dt.Rows(i)(17)
                        oITM.UserFields.Fields.Item("U_AB_UpdatedBy").Value = dt.Rows(i)(18)
                        oITM.UserFields.Fields.Item("U_AB_WWCompany").Value = dt.Rows(i)(19)

                        return_value = oITM.Add()
                        PublicVariable.oCompany.GetLastError(return_value, SerrorMsg)
                        If return_value = 0 Then
                            myService.UpdateItemInfo(CInt(dt.Rows(i)(0)), SerrorMsg)
                        Else
                            myService.UpdateItemInfo(CInt(dt.Rows(i)(0)), SerrorMsg)
                        End If

                    Else
                        oITM.GetByKey(dt.Rows(i)(1)) 'ds.Tables("row").Rows(i).Item(1)
                        oITM.ItemName = dt.Rows(i)(2) 'ds.Tables("row").Rows(i).Item(2)
                        oITM.ItemsGroupCode = ItemGroupCode(dt.Rows(i)(6)) 'ItemGroupCode(ds.Tables("row").Rows(i).Item(6))
                        oITM.ForeignName = dt.Rows(i)(1)
                        If dt.Rows(i)(3) = "Y" Then
                            oITM.PurchaseItem = SAPbobsCOM.BoYesNoEnum.tYES
                        Else
                            oITM.PurchaseItem = SAPbobsCOM.BoYesNoEnum.tNO
                        End If
                        If dt.Rows(i)(4) = "Y" Then
                            oITM.SalesItem = SAPbobsCOM.BoYesNoEnum.tYES
                        Else
                            oITM.SalesItem = SAPbobsCOM.BoYesNoEnum.tNO
                        End If
                        If dt.Rows(i)(5) = "Y" Then
                            oITM.InventoryItem = SAPbobsCOM.BoYesNoEnum.tYES
                        Else
                            oITM.InventoryItem = SAPbobsCOM.BoYesNoEnum.tNO
                        End If

                         oITM.UserFields.Fields.Item("U_AB_CreatedDate").Value = dt.Rows(i)(15)
                        oITM.UserFields.Fields.Item("U_AB_UpdateDate").Value = dt.Rows(i)(16)
                        oITM.UserFields.Fields.Item("U_AB_CreatedBy").Value = dt.Rows(i)(17)
                        oITM.UserFields.Fields.Item("U_AB_UpdatedBy").Value = dt.Rows(i)(18)
                        oITM.UserFields.Fields.Item("U_AB_WWCompany").Value = dt.Rows(i)(19)

                        return_value = oITM.Update()
                        PublicVariable.oCompany.GetLastError(return_value, SerrorMsg)
                        If return_value = 0 Then
                            myService.UpdateItemInfo(CInt(dt.Rows(i)(0)), SerrorMsg)
                        Else
                            myService.UpdateItemInfo(CInt(dt.Rows(i)(0)), SerrorMsg)
                        End If

                    End If
                    'oITM.Series = PublicVariable.Series
                   

                Catch ex As Exception
                    Functions.WriteLog("Class:oImport Function:ItemMasterData-For Loop:" & ex.Message)
                End Try
            Next
        Catch ex As Exception
            Functions.WriteLog("Class:oImport Function:ItemMasterData-" & ex.Message)
        End Try
    End Sub
    Public Function ItemGroupCode(ByVal ItemGroupName As String) As Integer
        Dim oRecordSetBP As SAPbobsCOM.Recordset
        oRecordSetBP = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
        oRecordSetBP.DoQuery("SELECT T0.[ItmsGrpCod] FROM OITB T0 WHERE T0.[ItmsGrpNam] ='" & ItemGroupName & "'")
        If oRecordSetBP.RecordCount > 0 Then
            Return oRecordSetBP.Fields.Item(0).Value
        Else
            Dim return_value As Integer
            Dim SerrorMsg As Integer
            Dim oITB As SAPbobsCOM.ItemGroups
            oITB = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oItemGroups)
            oITB.GroupName = ItemGroupName
            return_value = oITB.Add()
            PublicVariable.oCompany.GetLastError(return_value, SerrorMsg)
            If return_value <> 0 Then
                Return 100
            Else
                oRecordSetBP.DoQuery("SELECT T0.[ItmsGrpCod] FROM OITB T0 WHERE T0.[ItmsGrpNam] ='" & ItemGroupName & "'")
                Return oRecordSetBP.Fields.Item(0).Value
            End If
            Return 100
        End If

    End Function


    Public Function ItemCode(ByVal ItemName As String) As String
        Dim oRecordSetBP As SAPbobsCOM.Recordset
        oRecordSetBP = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
        oRecordSetBP.DoQuery("SELECT T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemName] ='" & ItemName & "'")
        If oRecordSetBP.RecordCount > 0 Then
            Return oRecordSetBP.Fields.Item(0).Value
        Else
            Return ""
        End If

    End Function
    'Private Sub EMail(ByVal FCustMaster As String, ByVal FCustname As String, ByVal FCustaddr As String, ByVal FCustSales As String, ByVal FCustRel_H As String, ByVal FIH As String, ByVal FIH1 As String, ByVal FCustRel_R As String, ByVal FIR As String, ByVal FIR1 As String, ByVal csvFileFolder As String)
    '    Try
    '        'Email
    '        If FCustMaster = "" And FCustname = "" And FCustaddr = "" And FCustSales = "" And FCustRel_H = "" And FIH = "" Then
    '            Exit Sub
    '        End If
    '        Dim ToAddress As String = ""
    '        Try


    '            Dim sPath As String = IO.Directory.GetParent(Application.StartupPath).ToString.Trim()
    '            Dim objIniFile As New INIClass(sPath.Replace("bin", "") & "\" & "ConfigFile.ini")
    '            PublicVariable.SMTPServer = objIniFile.GetString("SMTP", "SMTP_Server", "")
    '            PublicVariable.SMTPPort = objIniFile.GetString("SMTP", "SMTP_Port", "")
    '            PublicVariable.SMTPPwd = objIniFile.GetString("SMTP", "SMTP_Pwd", "")
    '            PublicVariable.SMTPEmail = objIniFile.GetString("SMTP", "SMTP_Email", "")
    '            PublicVariable.EmailList = objIniFile.GetString("SMTP", "EMAILLIST", "")
    '            '  PublicVariable.SMTPHost = objIniFile.GetString("SMTP", "SMTP_Host", "")
    '            'EmailList
    '            'SMTPHost
    '            ToAddress = PublicVariable.EmailList
    '        Catch ex As Exception
    '            '   ToAddress = "gokikopi@gmail.com"
    '        End Try
    '        Dim SmtpServer As New SmtpClient()
    '        SmtpServer.Credentials = New Net.NetworkCredential(PublicVariable.SMTPEmail, PublicVariable.SMTPPwd)
    '        SmtpServer.Port = PublicVariable.SMTPPort
    '        SmtpServer.Host = PublicVariable.SMTPServer
    '        SmtpServer.EnableSsl = True
    '        Dim mail As New MailMessage()
    '        mail = New MailMessage()
    '        Dim AttachmentFiles As ArrayList = Nothing
    '        Dim addr As String = ToAddress
    '        ' Dim j, iCnt As Integer

    '        Try
    '            mail.From = New MailAddress(PublicVariable.SMTPEmail, "HFS", System.Text.Encoding.UTF8)

    '            Dim i As Byte
    '            'For i = 0 To addr.Length - 1
    '            mail.To.Add(addr)
    '            'Next
    '            mail.Subject = "Viewpoint Integration Summary Log " & TDT1 & "  "
    '            mail.IsBodyHtml = True
    '            Dim Body1 As String = ""
    '            Dim Body2 As String = ""
    '            Dim Body3 As String = ""
    '            Dim Body4 As String = ""
    '            Dim Body5 As String = ""
    '            Dim Body6 As String = ""
    '            Dim Body7 As String = ""

    '            Dim Succ As String = ""
    '            If CMaster = True Then
    '                Body1 = "" & FCustMaster & "-File Name which has been successfuly uploaded into SAP"
    '            End If
    '            If CName = True Then
    '                Body2 = "" & FCustname & "-File Name which has been successfuly uploaded into SAP"
    '            End If
    '            If CAddr = True Then
    '                Body3 = "" & FCustaddr & "-File Name which has been successfuly uploaded into SAP"
    '            End If
    '            If CSales = True Then
    '                Body4 = "" & FCustSales & "-File Name which has been successfuly uploaded into SAP"
    '            End If
    '            If Crel = True Then
    '                Body5 = "" & FCustRel_H & "-File Name which has been successfuly uploaded into SAP"
    '            End If
    '            If SOH = True Then
    '                Body6 = "" & FIH & "-File Name which has been successfuly uploaded into SAP"
    '            End If
    '            If IH = True Then
    '                Body7 = "" & FIH1 & "-File Name which has been successfuly uploaded into SAP"
    '            End If
    '            If Body1 <> "" And FCustMaster <> "" Then
    '                Succ = Succ & Body1 & "<br />"
    '            End If
    '            If Body2 <> "" And FCustname <> "" Then
    '                Succ = Succ & Body2 & "<br />"
    '            End If
    '            If Body3 <> "" And FCustaddr <> "" Then
    '                Succ = Succ & Body3 & "<br />"
    '            End If
    '            If Body4 <> "" And FCustSales <> "" Then
    '                Succ = Succ & Body4 & "<br />"
    '            End If
    '            If Body5 <> "" And FCustRel_H <> "" Then
    '                Succ = Succ & Body5 & "<br />"
    '            End If
    '            If Body6 <> "" And FIH <> "" Then
    '                Succ = Succ & Body6 & "<br />"
    '            End If
    '            If Body7 <> "" And FIH1 <> "" Then
    '                Succ = Succ & Body7 & "<br />"
    '            End If
    '            Dim Body11 As String = ""
    '            Dim Body21 As String = ""
    '            Dim Body31 As String = ""
    '            Dim Body41 As String = ""
    '            Dim Body51 As String = ""
    '            Dim Body61 As String = ""
    '            Dim Body71 As String = ""

    '            Dim Fail As String = ""
    '            If CMaster = False Then
    '                Body11 = "" & FCustMaster & "-File name which has failed to upload and ErrorLog_Master" & TDT & ".txt of the file containing error information."
    '            End If
    '            If CName = False Then
    '                Body21 = "" & FCustname & "-File name which has failed to upload and ErrorLog_Name" & TDT & ".txt of the file containing error information."
    '            End If
    '            If CAddr = False Then
    '                Body31 = "" & FCustaddr & "-File name which has failed to upload and ErrorLog_Addr" & TDT & ".txt of the file containing error information."
    '            End If
    '            If CSales = False Then
    '                Body41 = "" & FCustSales & "-File name which has failed to upload and ErrorLog_Sales" & TDT & ".txt of the file containing error information."
    '            End If
    '            If Crel = False Then
    '                Body51 = "" & FCustRel_H & "-File name which has failed to upload and ErrorLog_CustRelation" & TDT & ".txt of the file containing error information."
    '            End If
    '            If SOH = False Then
    '                Body61 = "" & FIH & "-File name which has failed to upload and ErrorLog_ARInvoice_SOH" & TDT & ".txt of the file containing error information."
    '            End If
    '            If IH = False Then
    '                Body71 = "" & FIH1 & "-File name which has failed to upload and ErrorLog_ARInvoice_IH" & TDT & ".txt of the file containing error information."
    '            End If
    '            If Body11 <> "" And FCustMaster <> "" Then
    '                Fail = Fail & Body11 & "<br />"
    '            End If
    '            If Body21 <> "" And FCustname <> "" Then
    '                Fail = Fail & Body21 & "<br />"
    '            End If
    '            If Body31 <> "" And FCustaddr <> "" Then
    '                Fail = Fail & Body31 & "<br />"
    '            End If
    '            If Body41 <> "" And FCustSales <> "" Then
    '                Fail = Fail & Body41 & "<br />"
    '            End If
    '            If Body51 <> "" And FCustRel_H <> "" Then
    '                Fail = Fail & Body51 & "<br />"
    '            End If
    '            If Body61 <> "" And FIH <> "" Then
    '                Fail = Fail & Body61 & "<br />"
    '            End If
    '            If Body71 <> "" And FIH1 <> "" Then
    '                Fail = Fail & Body71 & "<br />"
    '            End If

    '            mail.Body = Succ & "<br />" & Fail
    '            '    <ol>
    '            '        <li>Test</li>
    '            '        <li>Test</li>
    '            '        <li>Test</li>
    '            '    </ol>
    '            '</div>"

    '            ' AttachmentFiles = "" & PublicVariable.LogFilePath & "ErrorLog_Master20130103.txt"
    '            'If Not AttachmentFiles Is Nothing Then
    '            '    iCnt = AttachmentFiles.Count - 1
    '            '    For i = 0 To iCnt
    '            '        If FileExists(AttachmentFiles(j)) Then _
    '            '          mail.Attachments.Add(AttachmentFiles(j))
    '            '    Next

    '            'End If

    '            Dim attachment As System.Net.Mail.Attachment
    '            attachment = New System.Net.Mail.Attachment("" & PublicVariable.LogFilePath & "\ErrorLog_Master" & TDT & ".txt")
    '            mail.Attachments.Add(attachment)
    '            '                Dim attachment As System.Net.Mail.Attachment
    '            attachment = New System.Net.Mail.Attachment("" & PublicVariable.LogFilePath & "\ErrorLog_Name" & TDT & ".txt")
    '            mail.Attachments.Add(attachment)
    '            attachment = New System.Net.Mail.Attachment("" & PublicVariable.LogFilePath & "\ErrorLog_Addr" & TDT & ".txt")
    '            mail.Attachments.Add(attachment)
    '            attachment = New System.Net.Mail.Attachment("" & PublicVariable.LogFilePath & "\ErrorLog_Sales" & TDT & ".txt")
    '            mail.Attachments.Add(attachment)
    '            attachment = New System.Net.Mail.Attachment("" & PublicVariable.LogFilePath & "\ErrorLog_CustRelation" & TDT & ".txt")
    '            mail.Attachments.Add(attachment)
    '            attachment = New System.Net.Mail.Attachment("" & PublicVariable.LogFilePath & "\ErrorLog_ARInvoice_SOH" & TDT & ".txt")
    '            mail.Attachments.Add(attachment)
    '            attachment = New System.Net.Mail.Attachment("" & PublicVariable.LogFilePath & "\ErrorLog_ARInvoice_IH" & TDT & ".txt")
    '            mail.Attachments.Add(attachment)
    '            mail.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure
    '            mail.ReplyTo = New MailAddress(PublicVariable.SMTPEmail)
    '            SmtpServer.Send(mail)
    '            mail.Dispose()
    '        Catch ex As Exception
    '            mail.Dispose()
    '            Functions.WriteLog(ex.ToString)
    '        End Try
    '        '
    '        Try
    '            If FCustMaster <> "" And CMaster = True Then
    '                FileMove(FCustMaster, PublicVariable.SuccessFolder & FCustMaster.Replace(csvFileFolder, ""))
    '            ElseIf FCustMaster <> "" And CMaster = False Then
    '                FileMove(FCustMaster, PublicVariable.ErrorFolder & FCustMaster.Replace(csvFileFolder, ""))
    '            End If
    '            If FCustname <> "" And CName = True Then
    '                FileMove(FCustname, PublicVariable.SuccessFolder & FCustname.Replace(csvFileFolder, ""))
    '            ElseIf FCustname <> "" And CName = False Then
    '                FileMove(FCustname, PublicVariable.ErrorFolder & FCustname.Replace(csvFileFolder, ""))
    '            End If
    '            If FCustaddr <> "" And CAddr = True Then
    '                FileMove(FCustaddr, PublicVariable.SuccessFolder & FCustaddr.Replace(csvFileFolder, ""))
    '            ElseIf FCustaddr <> "" And CAddr = False Then
    '                FileMove(FCustaddr, PublicVariable.ErrorFolder & FCustaddr.Replace(csvFileFolder, ""))
    '            End If
    '            If FCustSales <> "" And CSales = True Then
    '                FileMove(FCustSales, PublicVariable.SuccessFolder & FCustSales.Replace(csvFileFolder, ""))
    '            ElseIf FCustSales <> "" And CSales = False Then
    '                FileMove(FCustSales, PublicVariable.ErrorFolder & FCustSales.Replace(csvFileFolder, ""))
    '            End If
    '            If FCustRel_H <> "" And Crel = True Then
    '                FileMove(FCustRel_H, PublicVariable.SuccessFolder & FCustRel_H.Replace(csvFileFolder, ""))
    '            ElseIf FCustRel_H <> "" And Crel = False Then
    '                FileMove(FCustRel_H, PublicVariable.ErrorFolder & FCustRel_H.Replace(csvFileFolder, ""))
    '            End If
    '            If FCustRel_R <> "" And Crel = True Then
    '                FileMove(FCustRel_R, PublicVariable.SuccessFolder & FCustRel_R.Replace(csvFileFolder, ""))
    '            ElseIf FCustRel_R <> "" And Crel = False Then
    '                FileMove(FCustRel_R, PublicVariable.ErrorFolder & FCustRel_R.Replace(csvFileFolder, ""))
    '            End If
    '            If FIH <> "" And SOH = True Then
    '                FileMove(FIH, PublicVariable.SuccessFolder & FIH.Replace(csvFileFolder, ""))
    '            ElseIf FIH <> "" And SOH = False Then
    '                FileMove(FIH, PublicVariable.ErrorFolder & FIH.Replace(csvFileFolder, ""))
    '            End If
    '            If FIR <> "" And SOH = True Then
    '                FileMove(FIR, PublicVariable.SuccessFolder & FIR.Replace(csvFileFolder, ""))
    '            ElseIf FIR <> "" And SOH = False Then
    '                FileMove(FIR, PublicVariable.ErrorFolder & FIR.Replace(csvFileFolder, ""))
    '            End If
    '            If FIH1 <> "" And IH = True Then
    '                FileMove(FIH1, PublicVariable.SuccessFolder & FIH1.Replace(csvFileFolder, ""))
    '            ElseIf FIH1 <> "" And IH = False Then
    '                FileMove(FIH1, PublicVariable.ErrorFolder & FIH1.Replace(csvFileFolder, ""))
    '            End If
    '        Catch ex As Exception
    '            Functions.WriteLog(ex.ToString)
    '        End Try
    '    Catch ex As Exception
    '    End Try
    'End Sub

End Class
